Oracle OGG 实时数据分发

#Oracle #OGG #null #实时数据分发

1. 使用OGG 进行实时数据分发

1 数据分发配置概述

数据分发配置是1 对多配置.OGG 将源数据库同步到任意数量的目标系统.OGG 支持like-to-like 或异构数据库传输.

2 数据分发配置注意事项

2.1 容错能力

对于数据分发配置,在原系统上使用数据泵可以确保,如果任何目标端的接连失败,捕获的数据仍然可以发送到其他目标端.为每个目标端都配置使用一个extract 和datapump 提取组.

2.2 过滤和转换

可以使用任何进程执行筛选和转换.但,还是建议使用数据泵执行过滤操作,可以减少提取程序的开销,并减少网络数据.

2.3 只读与高可用性

数据分发配置支持只读目标.如果此配置支持高可用双中心的话.

2.4 附加信息

操作系统需求,流程配置和数据库设置需求.
配置OGG extract 和replicat 的详细说明
关于OGG 性能调优选项.
有关OGG 命令和参数完整语法和描述.

3 创建数据分发配置

图6-1 1数据分发的配置示意图

3.1 源端系统

配置manager

在目标端上,根据manager 和网络通信说明配置manager 进程.
在manager 参数文件中,使用 PURGEOLDEXTRACTS 控制本体trail 文件清除.

**在源端配置主提取组 **

注册extract

DBLOGIN USERIDALIAS citdb
UNREGISTER EXTRACT ehr, DATABASE
REGISTER EXTRACT ehr, DATABASE

在源端系统上使用ADD EXTRACT 命令创建提取组.

ADD EXTRACT ext, {TRANLOG | INTEGRATED TRANLOG}, BEGIN time [option[, ...]]

DELETE EXTRACT ehr
ADD EXTRACT ehr , INTEGRATED TRANLOG ,BEGIN NOW

在源端系统上,使用ADD EXTTRAIL 命令

ADD EXTTRAIL ./dirdat/lt, EXTRACT ehr

在源端上,使用EDIT PARAMS 命令创建参数文件.

-- Identify the Extract group:
EXTRACT ehr
-- Specify database login information as needed for the database:
-- [SOURCEDB dsn_1][, USERIDALIAS alias]
USERIDALIAS citdb
-- Log all scheduling columns if using integrated Replicat
LOGALLSUPCOLS
-- Specify the local trail that this Extract writes to and
-- encryption algorithm:
-- ENCRYPTTRAIL algorithm
EXTTRAIL ./dirdat/lt
-- Specify tables and sequences to be captured:
-- SEQUENCE [container.|catalog.]owner.sequence;
-- TABLE [container.|catalog.]owner.table;
TABLE HR.*

EXTRACT ehr
--- User login
USERIDALIAS citdb
DISCARDFILE ./dirrpt/eapps.dsc, APPEND
DISCARDROLLOVER AT 01:00 ON SUNDAY
EXTTRAIL ./dirdat/lt
STATOPTIONS REPORTFETCH
REPORTCOUNT every 10 minutes, RATE
REPORTROLLOVER AT 01:00 ON SUNDAY
--- DDL Parameters
DDL INCLUDE MAPPED
DDLOPTIONS REPORT
TABLE HR.* ;

配置源端数据泵

在源端上,使用ADD EXTRACT 命令创建一个数据库泵.

ADD EXTRACT pump_1, EXTTRAILSOURCE local_trail, BEGIN time
ADD EXTRACT pump_2, EXTTRAILSOURCE local_trail, BEGIN time

ADD EXTRACT phr1, EXTTRAILSOURCE ./dirdat/lt, BEGIN NOW
ADD EXTRACT phr2, EXTTRAILSOURCE ./dirdat/lt, BEGIN NOW

在源端系统上使用ADD RMTTRAIL 命令指定系统创建的远程trail .

ADD RMTTRAIL remote_trail_1, EXTRACT pump_1
ADD RMTTRAIL remote_trail_2, EXTRACT pump_2

ADD RMTTRAIL ./dirdat/rt1, EXTRACT phr1
ADD RMTTRAIL ./dirdat/rt2, EXTRACT phr2

编辑参数
example 1:

-- Identify the data pump group:
EXTRACT pump_1
-- Specify database login information:
[SOURCEDB dsn_1][, USERIDALIAS alias]
-- Decrypt the data only if the data pump must process it.
-- DECRYPTTRAIL
-- Specify the name or IP address of the first target system
-- and optional encryption of data over TCP/IP:
RMTHOSTOPTIONS target_1, MGRPORT port_number, ENCRYPT encryption_options
-- Specify remote trail and encryption algorithm on first target system:
ENCRYPTTRAIL algorithm
RMTTRAIL remote_trail_1
-- Specify tables and sequences to be captured:
SEQUENCE [container.|catalog.]owner.sequence;
TABLE [container.|catalog.]owner.table;

EXTRACT phr 
RMTHOST 192.168.10.219, MGRPORT 7801
PASSTHRU
RMTTRAIL ./dirdat/rt
TABLE HR.* ;

example 2:

-- Identify the data pump group:
EXTRACT pump_2
-- Specify database login information as needed for the database:
[SOURCEDB dsn_1][, USERIDALIAS alias]
-- Decrypt the data only if the data pump must process it.
-- DECRYPTTRAIL
-- Specify the name or IP address of the second target system
-- and optional encryption of data over TCP/IP:
RMTHOSTOPTIONS target_2, MGRPORT port_number, ENCRYPT encryption_options
-- Specify remote trail and encryption algorithm on second target system:
ENCRYPTTRAIL algorithm
RMTTRAIL remote_trail_2
-- Specify tables and sequences to be captured:
SEQUENCE [container.|catalog.]owner.sequence;
TABLE [container.|catalog.]owner.table;

EXTRACT phr 
RMTHOST 192.168.10.219, MGRPORT 7801
PASSTHRU
RMTTRAIL ./dirdat/rt
TABLE HR.* ;

3.2 目标端

在目标系统喊配置manager 进程管理器和replicat.
配置manager

在目标端上,根据manager 和网络通信说明配置manager 进程.
在manager 参数文件中,使用 PURGEOLDEXTRACTS 控制本体trail 文件清除.
在目标端配置replicat 组
在每个目标端上,创建一个replicat 检查点(除非使用OGG 集成模式).
在每个目标端上,使用ADD REPLICAT 命令创建replicat 组.

target 1:

ADD REPLICAT rep_1 
[, INTEGRATED | COORDINATED [MAXTHREADS number]]
, EXTTRAIL remote_trail_1, BEGIN time

target 2:

ADD REPLICAT rep_2
[, INTEGRATED | COORDINATED [MAXTHREADS number]]
, EXTTRAIL remote_trail_2, BEGIN time

DBLOGIN USERIDALIAS citdb
delete rhr
ADD REPLICAT rhr, INTEGRATED, EXTTRAIL ./dirdat/rt1,BEGIN NOW

DBLOGIN USERIDALIAS citdb
delete rhr
ADD REPLICAT rhr, INTEGRATED, EXTTRAIL ./dirdat/rt2,BEGIN NOW

在目标系统上,使用EDIT PARAMS 命令创建参数文件.

target 1:

-- Identify the Replicat group:
REPLICAT rep_1
-- Specify database login information as needed for the database:
[TARGETDB dsn_2][, USERIDALIAS alias]
-- Specify error handling rules:
REPERROR (error, response)
-- Specify tables for delivery and threads if using coordinated Replicat:
MAP [container.|catalog.]owner.table, TARGET owner.table[, DEF template]
[, THREAD (thread_ID)][, THREADRANGE (thread_range[, column_list])]
;

REPLICAT rhr
--- ASSUMETARGETDEFS is ignored in OGG 12.2
ASSUMETARGETDEFS
DISCARDFILE ./dirrpt/rapps.dsc, APPEND
--- User login
USERIDALIAS citdb
REPORTCOUNT EVERY 30 MINUTES, RATE
REPORTROLLOVER AT 01:00 ON SUNDAY
DISCARDROLLOVER AT 01:00 ON SUNDAY

--MAP_PARALLELISM 3
--MIN_APPLY_PARALLELISM 2
--MAX_APPLY_PARALLELISM 10
--SPLIT_TRANS_RECS 1000

--- DDL Parameters
DDL INCLUDE ALL
DDLOPTIONS REPORT
MAP HR.*, TARGET DSG.*;

target 2:

-- Identify the Replicat group:
REPLICAT rep_2
-- Specify database login information as needed for the database:
[TARGETDB dsn_3][, USERIDALIAS alias]
-- Specify error handling rules:
REPERROR (error, response)
-- Specify tables for delivery and threads if using coordinated Replicat:
MAP [container.|catalog.]owner.table, TARGET owner.table[, DEF template]
[, THREAD (thread_ID)][, THREADRANGE (thread_range[, column_list])]
;

REPLICAT rhr
--- ASSUMETARGETDEFS is ignored in OGG 12.2
ASSUMETARGETDEFS
DISCARDFILE ./dirrpt/rapps.dsc, APPEND
--- User login
USERIDALIAS citdb
REPORTCOUNT EVERY 30 MINUTES, RATE
REPORTROLLOVER AT 01:00 ON SUNDAY
DISCARDROLLOVER AT 01:00 ON SUNDAY

--MAP_PARALLELISM 3
--MIN_APPLY_PARALLELISM 2
--MAX_APPLY_PARALLELISM 10
--SPLIT_TRANS_RECS 1000

--- DDL Parameters
DDL INCLUDE ALL
DDLOPTIONS REPORT
MAP HR.*, TARGET DSG.*;